﻿using System;
using System.Collections.Generic;
using System.Text;
using Estimate.Common.DBFieldTypes;
using System.Data.SqlClient;
using Estimate.Common.Entity;

namespace Estimate.Common.Repository
{
    /// <summary>
    /// 实况数据入库记录表
    /// </summary>
    public class RealDataLogRepository : BaseRepository
    {
        public RealDataLogRepository(string connectionString):base(connectionString,"RealDataLog")
        {
        }

        public override string GetCreateTableSql()
        {
            string sqlcreate = String.Format("CREATE TABLE [{0}] (id bigint identity(1,1), realtype varchar(6), lastimporttime datetime, realdate date,needreimport bit, reimportcount	int,error int, errorstring text null, lack int,lackstring text null, filepath varchar(200) ) \n", _tableName);
            sqlcreate += String.Format("CREATE CLUSTERED INDEX [{0}_INDEX] ON [{1}] (lastimporttime,realtype,realdate,needreimport,reimportcount,error,lack) \n", _tableName, _tableName);
            sqlcreate += String.Format("alter table [{0}] add primary key (id)", _tableName);
            return sqlcreate;
        }       

        /// <summary>
        /// 查看月度报表日期是否存在
        /// </summary>
        ///<param name="realDate">实况文件的日期</param>
        ///<param name="realtype">实况的类型</param>
        ///<return>实况记录是否存在</return>
        public bool IsRecordExist(ForeSemiType realtype,DateTime realDate)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE realdate=@realdate and realtype=@realtype", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@realdate", realDate.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@realtype", realtype.ToString());
            return DBHelper.Exists(_connectionString, sql, param);
        }

        public void UpdateRecord(DateTime realDate, ForeSemiType realtype, DateTime lastImportTime, int reimportCount, int error, string errorstring, int lack, string lackstring, string path)
        {
            string sql = String.Format("UPDATE [{0}] SET lastimporttime=@lastimporttime, needreimport=@needreimport,reimportcount=@reimportcount,error=@error,errorstring=@errorstring,lack=@lack,lackstring=@lackstring,filepath=@filepath WHERE  realtype=@realtype and realdate=@realdate", _tableName);
            SqlParameter[] param = new SqlParameter[10];
            param[0] = new SqlParameter("@realdate", realDate.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@realtype", realtype.ToString());
            param[2] = new SqlParameter("@lastimporttime", lastImportTime.ToString("yyyy-MM-dd hh:mm:ss"));
            param[3] = new SqlParameter("@needreimport", RealDataLogEntity.IsNeedReimport(error, lack));
            param[4] = new SqlParameter("@reimportCount", reimportCount);
            param[5] = new SqlParameter("@error", error);
            param[6] = new SqlParameter("@errorstring", errorstring);
            param[7] = new SqlParameter("@lack", lack);
            param[8] = new SqlParameter("@lackstring", lackstring);
            param[9] = new SqlParameter("@filepath", path);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        public void InsertRecord(DateTime realDate, ForeSemiType realtype, DateTime lastImportTime, int reimportCount, int error, string errorstring, int lack, string lackstring, string path)
        {
            string sql = String.Format("INSERT INTO [{0}] (realdate,realtype,lastimporttime,needreimport,reimportcount,error,errorstring, lack,lackstring,filepath) values(@realdate ,@realtype ,@lastimporttime ,@needreimport,@reimportcount,@error,@errorstring,@lack,@lackstring,@filepath)", _tableName);
            SqlParameter[] param = new SqlParameter[10];
            param[0] = new SqlParameter("@realdate", realDate.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@realtype", realtype.ToString());
            param[2] = new SqlParameter("@lastimporttime", lastImportTime.ToString("yyyy-MM-dd hh:mm:ss"));
            param[3] = new SqlParameter("@needreimport", RealDataLogEntity.IsNeedReimport(error, lack));
            param[4] = new SqlParameter("@reimportCount", reimportCount);
            param[5] = new SqlParameter("@error", error);
            param[6] = new SqlParameter("@errorstring", errorstring);
            param[7] = new SqlParameter("@lack", lack);
            param[8] = new SqlParameter("@lackstring", lackstring);
            param[9] = new SqlParameter("@filepath", path);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        public void SetRecord(DateTime realDate, ForeSemiType realtype, DateTime lastImportTime, int reimportCount, int error, string errorstring, int lack, string lackstring, string path) 
        {
            if (IsRealDataExist(realtype,realDate))
            {
                UpdateRecord(realDate, realtype, lastImportTime, reimportCount, error, errorstring, lack, lackstring, path);
            }
            else
            {
                InsertRecord(realDate, realtype, lastImportTime, reimportCount, error, errorstring, lack, lackstring, path);
            }
        }

        public void SetRecord(RealDataLogEntity entity)
        {
            SetRecord(entity.RealDate, entity.RealType, entity.LastImportTime, entity.ReimportCount, entity.Error,entity.ErrorString, entity.Lack, entity.LackString, entity.FilePath);
        }


        /// <summary>
        /// 获取最后一条入库数据
        /// </summary>
        /// <param name="realDataType">指定的实况数据类型</param>
        /// <returns></returns>
        public Entity.RealDataLogEntity GetLatestRealData(ForeSemiType realDataType)
        {
            string sql = String.Format("SELECT top(1) * FROM [{0}] WHERE realtype=@realtype ORDER BY id desc", _tableName);
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@realtype", realDataType.ToString());
           
            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);

            Entity.RealDataLogEntity result = new Entity.RealDataLogEntity();

            if (reader.Read())
            {
                result.RealType = (ForeSemiType)Enum.Parse(typeof(ForeSemiType), reader["realtype"].ToString());
                result.LastImportTime = DateTime.Parse( reader["lastimporttime"].ToString() );
                result.RealDate = DateTime.Parse( reader["realdate"].ToString());                
                result.ReimportCount = Convert.ToInt32(reader["reimportcount"]);
                result.Error = Convert.ToInt32(reader["error"]);
                result.ErrorString = reader["errorstring"].ToString();
                result.Lack = Convert.ToInt32(reader["lack"]);
                result.LackString = reader["lackstring"].ToString();
                result.FilePath = reader["filepath"].ToString();

                connection.Close();
                return result;
            }
            return result;
        }

        /// <summary>
        /// 获取指定日期的实况文件入库情况 
        /// </summary>
        /// <param name="realDataType">指定的实况数据类型</param>
        /// <param name="realdate">指定的日期</param>
        /// <returns></returns>
        public Entity.RealDataLogEntity GetRealDataWithDate(ForeSemiType realDataType,DateTime realdate)
        {
            string sql = String.Format("SELECT top(1) * FROM [{0}] WHERE realtype=@realtype and realdate=@realdate", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@realtype", realDataType.ToString());
            param[1] = new SqlParameter("@realdate", realdate.ToString());

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);

            Entity.RealDataLogEntity result = new Entity.RealDataLogEntity();

            if (reader.Read())
            {
                result.RealType = (ForeSemiType)Enum.Parse(typeof(ForeSemiType), reader["realtype"].ToString());
                result.LastImportTime = DateTime.Parse(reader["lastimporttime"].ToString());
                result.RealDate = DateTime.Parse(reader["realdate"].ToString());
                result.ReimportCount = Convert.ToInt32(reader["reimportcount"]);
                result.Error = Convert.ToInt32(reader["error"]);
                result.ErrorString = reader["errorstring"].ToString();
                result.Lack = Convert.ToInt32(reader["lack"]);
                result.LackString = reader["lackstring"].ToString();
                result.FilePath = reader["filepath"].ToString();

                connection.Close();
                return result;
            }
            return result;
        }

        /// <summary>
        /// 获取指定日期的实况文件入库情况 
        /// </summary>
        /// <param name="realDataType">指定的实况数据类型</param>
        /// <param name="realdate">指定的日期</param>
        /// <returns></returns>
        public Entity.RealDataLogEntity[] GetRealDataWithDateRange(ForeSemiType realDataType, DateTime begeinRealDate, DateTime endRealDate)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE realtype=@realtype and realdate >= @begin and realdate <= @end ORDER BY realdate", _tableName);
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@realtype", realDataType.ToString());
            param[1] = new SqlParameter("@begin", begeinRealDate.ToString());
            param[2] = new SqlParameter("@end", endRealDate.ToString());

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);

            List<RealDataLogEntity> result = new List<RealDataLogEntity>();

            while (reader.Read())
            {
                Entity.RealDataLogEntity entity = new Entity.RealDataLogEntity();
                entity.RealType = (ForeSemiType)Enum.Parse(typeof(ForeSemiType), reader["realtype"].ToString());
                entity.LastImportTime = DateTime.Parse(reader["lastimporttime"].ToString());
                entity.RealDate = DateTime.Parse(reader["realdate"].ToString());
                entity.ReimportCount = Convert.ToInt32(reader["reimportcount"]);
                entity.Error = Convert.ToInt32(reader["error"]);
                entity.ErrorString = reader["errorstring"].ToString();
                entity.Lack = Convert.ToInt32(reader["lack"]);
                entity.LackString = reader["lackstring"].ToString();
                entity.FilePath = reader["filepath"].ToString();
                result.Add(entity);                                
            }
            connection.Close();
            return result.ToArray();
        }

        /// <summary>
        /// 根据实况类型及计算时间，查看是否存在同样记录
        /// </summary>
        /// <param name="realDataType">实况数据类型</param>
        /// <param name="calDate">实况数据入库时间</param>
        /// <returns>是否存在</returns>
        public bool IsRealDataExist(ForeSemiType realDataType, DateTime calDate)
        {
            string sql = String.Format("SELECT id FROM [{0}] WHERE realdate=@realdate  and realtype=@realtype", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@realdate", calDate.ToString("yyyy-MM-dd"));
            param[1] = new SqlParameter("@realtype", realDataType.ToString());
           
            return DBHelper.Exists(_connectionString, sql, param);
        }


    }
}
